package cn.sciento.transfer.infra.datasource.ddl;

import cn.sciento.core.exception.CommonException;import java.io.StringWriter;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.velocity.Template;
import org.apache.velocity.VelocityContext;
import org.apache.velocity.app.Velocity;
import org.apache.velocity.context.Context;
import cn.sciento.transfer.infra.datasource.ddl.sqlserver.dto.ColumnEntity;
import cn.sciento.transfer.infra.datasource.ddl.sqlserver.dto.IndexEntity;
import cn.sciento.transfer.infra.datasource.ddl.sqlserver.dto.IndexType;
import cn.sciento.transfer.infra.datasource.ddl.sqlserver.dto.TableEntity;

public class SqlServerTableScriptBuilder extends BaseTableScriptBuilder {
  public static final String TEMPLATE_NAME = "template/BuildTableDDL.vm";
  
  public static final String DEFAULT_ENCODING = "UTF-8";
  
  public String getCreateTableScript(Connection connection, String tableName) throws SQLException {
    List<Map<String, Object>> tableEntryMaps = doQuery(connection, String.format("SELECT%n\tsb.name AS table_name,%n\t cast(sep.value as nvarchar(500))   AS table_comment %nFROM%n\tsysobjects sb%n\tLEFT JOIN sys.extended_properties sep ON sb.id = sep.major_id %n\tAND sep.minor_id = 0 %nWHERE%nsb.name = '%s' ", new Object[] { tableName }), Boolean.valueOf(false));
    TableEntity tableEntry = TableEntity.build(tableEntryMaps.get(0));
    List<Map<String, Object>> columnEntityMaps = doQuery(connection, String.format("SELECT%n\tsc.NAME AS column_name,%n\tcast( sep.VALUE AS NVARCHAR ) AS column_comment,%n\tst.NAME AS column_type ,%n\tsc.length AS column_size,%n\tsc.isnullable AS is_null_able,%n\tCOLUMNPROPERTY( sc.id,sc.name,'IsIdentity') AS is_identity,%n\tsct.text AS column_default %nFROM%n\tsyscolumns sc%n\tLEFT JOIN syscomments sct ON sc.cdefault = sct.id%n\tLEFT JOIN sys.extended_properties sep ON sc.id = sep.major_id %n\tAND sc.colid = sep.minor_id%n\tLEFT JOIN systypes st ON sc.xusertype = st.xusertype %nWHERE%n\tsc.id = ( SELECT sb.id FROM sysobjects sb WHERE sb.NAME = '%s' AND sb.xtype = 'U' )", new Object[] { tableName }), Boolean.valueOf(false));
    List<ColumnEntity> columnEntitys = ColumnEntity.build(columnEntityMaps);
    List<Map<String, Object>> indexEntityMap = doQuery(connection, String.format("EXEC Sp_helpindex %s", new Object[] { tableName }), Boolean.valueOf(false));
    List<IndexEntity> indexEntitys = IndexEntity.build(indexEntityMap);
    Properties prop = new Properties();
    prop.put("file.resource.loader.class", "org.apache.velocity.runtime.resource.loader.ClasspathResourceLoader");
    Velocity.init(prop);
    Map<String, Object> map = new HashMap<>();
    map.put("tableEntity", tableEntry);
    map.put("columnEntitys", columnEntitys);
    map.put("indexEntitys", indexEntitys);
    VelocityContext context = new VelocityContext(map);
    Template template = Velocity.getTemplate("template/BuildTableDDL.vm", "UTF-8");
    StringWriter writer = new StringWriter();
    template.merge((Context)context, writer);
    return writer.toString();
  }
  
  public List<String> getTablePrimaryKey(Connection connection, String schemaName, String tableName) throws SQLException {
    List<Map<String, Object>> indexMaps = doQuery(connection, String.format("EXEC Sp_helpindex %s", new Object[] { tableName }), Boolean.valueOf(false));
    if (CollectionUtils.isEmpty(indexMaps))
      throw new CommonException("can not find either of primaryKey and uniqueKey from table:" + tableName);
    List<String> primaryKey = findPrimaryKey(indexMaps);
    if (CollectionUtils.isEmpty(primaryKey))
      throw new CommonException("can not find either of primaryKey and uniqueKey from table:" + tableName);
    return primaryKey;
  }
  
  private List<String> findPrimaryKey(List<Map<String, Object>> indexMaps) {
    List<String> primaryKeys = new ArrayList<>(1);
    List<String> uniqueKeys = new ArrayList<>(1);
    for (Map<String, Object> indexMap : indexMaps) {
      if (IndexType.primary.equals(judgeIndexType(indexMap))) {
        String indexColumnNames = (String)indexMap.get("index_keys");
        primaryKeys = parsePrimaryKey(indexColumnNames);
        continue;
      } 
      if (IndexType.unique.equals(judgeIndexType(indexMap))) {
        String indexColumnNames = (String)indexMap.get("index_keys");
        uniqueKeys = parsePrimaryKey(indexColumnNames);
      } 
    } 
    if (CollectionUtils.isNotEmpty(primaryKeys))
      return primaryKeys; 
    if (CollectionUtils.isNotEmpty(uniqueKeys))
      return uniqueKeys; 
    return null;
  }
  
  private IndexType judgeIndexType(Map<String, Object> indexMap) {
    String indexDescription = (String)indexMap.get("index_description");
    if (indexDescription.contains("clustered") && indexDescription.contains("primary key"))
      return IndexType.primary; 
    if (indexDescription.contains("nonclustered") && indexDescription.contains("unique key"))
      return IndexType.unique; 
    return IndexType.general;
  }
}
